﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Data.SQLite;
using System.Text;
using System.Threading.Tasks;

namespace H.Y.Clipboard
{
	class DatabaseOperator
	{
		private static String TABLE_NAME_CONTENT = "Content";
		private static String TABLE_NAME_USER = "User";

		public class User {
			public string uid { get; set; }
			public string nickname { get; set; }
			public string account { get; set; }
			public int signed { get; set; }
		}

		public class ClipboardItem
		{
			public String cid { get; set; }
			public String time { get; set; }
			public String device { get; set; }
			public int read { get; set; }
			public String content { get; set; }
		}

		static string DbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "H.Y.Clipboard");
		private static SQLiteConnection dbConn = CreateDatabaseConnection();


		private static SQLiteConnection CreateDatabaseConnection(string dbName = null)
		{
			if (!string.IsNullOrEmpty(DbPath) && !Directory.Exists(DbPath))
			{
				Directory.CreateDirectory(DbPath);
			}
			dbName = dbName == null ? Environment.MachineName : dbName;
			var dbFilePath = Path.Combine(DbPath, dbName);
			return new SQLiteConnection("DataSource = " + dbFilePath);
		}


		private static void Open(SQLiteConnection conn)
		{
			if (conn.State != System.Data.ConnectionState.Open)
			{
				conn.Open();
			}
		}

		private static void ExecuteNonQuery(string sql)
		{
			Open(dbConn);
			
			using (var tr = dbConn.BeginTransaction())
			{
				using (var command = dbConn.CreateCommand())
				{
					command.CommandText = sql;
					command.ExecuteNonQuery();
				}
				tr.Commit();
			}
		}

		public static void createContentTable()
		{
			string sql = @"CREATE TABLE IF NOT EXISTS `Content` (`cid` varchar(36) NOT NULL, `content` text NOT NULL, `time` timestamp NOT NULL, `read` tinyint(1) NOT NULL, `device` varchar(50) DEFAULT);";
			ExecuteNonQuery(sql);
		}

		public static void createUserTable()
		{
			string sql = @"CREATE TABLE IF NOT EXISTS `User` (
  `uid` varchar(36) NOT NULL,
  `nickname` varchar(20) NOT NULL,
  `account` varchar(18) NOT NULL,
  `signed` tinyint(1) NOT NULL
);";
			ExecuteNonQuery(sql);
		}

		public static void insertContent(ClipboardItem memoData)
		{
			String sql = String.Format("insert into {0} ({1}, {2}, {3}, {4}, {5}) values (''{6}'', ''{7}'', ''{8}'', {9}, ''{10}'')",
				TABLE_NAME_CONTENT, Macros.DBTags.CID, Macros.DBTags.CONTENT, Macros.DBTags.TIME, Macros.DBTags.READ, Macros.DBTags.DEVICE,
				memoData.cid, memoData.content, memoData.time, memoData.read, memoData.device);
			ExecuteNonQuery(sql);
		}

		public static void deleteAllContent()
		{
			String sql = String.Format("TRUNCATE TABLE {0};", TABLE_NAME_CONTENT);
			ExecuteNonQuery(sql);
		}

		public static List<ClipboardItem> queryAllContent()
		{
			List<ClipboardItem> memoDataList = new List<ClipboardItem>();
			Open(dbConn);
			using(var tr = dbConn.BeginTransaction())
			{
				using(var command = dbConn.CreateCommand())
				{
					command.CommandText = String.Format("select * from `{0}` order by `{1}` desc", TABLE_NAME_CONTENT, Macros.DBTags.TIME);
					var reader = command.ExecuteReader();
					while (reader.Read())
					{
						ClipboardItem memoData = new ClipboardItem();
						memoData.cid = reader.GetString(0);
						memoData.content = reader.GetString(1);
						memoData.time = reader.GetString(2);
						memoData.read = reader.GetInt16(3);
						memoData.device = reader.GetString(4);
						memoDataList.Add(memoData);
					}
					reader.Close();
				}
				tr.Commit();
			}
			return memoDataList;
		}

		public static void insertUser(User user)
		{
			string sql = string.Format("insert into {0} (`{1}`, `{2}`, `{3}`, `{4}`) values ('{5}', '{6}', '{7}', '{8}')",
				TABLE_NAME_USER, Macros.DBTags.UID, Macros.DBTags.NICKNAME, Macros.DBTags.ACCOUNT, Macros.DBTags.SIGNED,
				user.uid, user.nickname, user.account, user.signed);
			ExecuteNonQuery(sql);
		}

		public static void deleteUser(string uid="", string account="")
		{
			string sql = "";
			if (uid != "")
			{
				sql = string.Format("delete from {0} where `uid` = '{1}';", TABLE_NAME_USER, uid);
			}else if (account != "")
			{
				sql = string.Format("delete from {0} where `uid` = '{1}';", TABLE_NAME_USER, account);
			}
			ExecuteNonQuery(sql);
		}

		public static User queryUser(string uid = "", string account = "")
		{
			if (uid == "" && account == "") return null;
			User user = new User();
			Open(dbConn);
			using (var tr = dbConn.BeginTransaction())
			{
				using(var command = dbConn.CreateCommand())
				{
					if(uid != "")
					{
						command.CommandText = String.Format("select * from `{0}` where `{1}` = '{2}'", TABLE_NAME_USER, Macros.DBTags.UID, uid);
					} else if (account != "")
					{
						command.CommandText = String.Format("select * from `{0}` where `{1}` = '{2}'", TABLE_NAME_USER, Macros.DBTags.ACCOUNT, account);
					}
					var reader = command.ExecuteReader();
					while (reader.Read())
					{
						user.uid = reader.GetString(0);
						user.nickname = reader.GetString(1);
						user.account = reader.GetString(2);
						user.signed = reader.GetInt16(3);
						break;
					}
					reader.Close();
				}
				tr.Commit();
			}
			return user;
		}

		public static User querySignedUser()
		{
			User user = new User();
			Open(dbConn);
			using (var tr = dbConn.BeginTransaction())
			{
				using (var command = dbConn.CreateCommand())
				{
					command.CommandText = String.Format("select * from `{0}` where `{1}` = '{2}'", TABLE_NAME_USER, Macros.DBTags.SIGNED, 1);
					var reader = command.ExecuteReader();
					while (reader.Read())
					{
						user.uid = reader.GetString(0);
						user.nickname = reader.GetString(1);
						user.account = reader.GetString(2);
						user.signed = reader.GetInt16(3);
						break;
					}
					reader.Close();
				}
				tr.Commit();
			}
			return user;
		}

		public static void setUserSigned(string uid)
		{
			string sql = String.Format("update `{0}` set `{1}` = '{2}' where `uid` = '{3}'", TABLE_NAME_USER, Macros.DBTags.SIGNED, 1, uid);
			ExecuteNonQuery(sql);
		}

		public static void setAllUserUnsigned()
		{
			string sql = String.Format("update `{0}` set `{1}` = '{2}' where 1", TABLE_NAME_USER, Macros.DBTags.SIGNED, 0);
			ExecuteNonQuery(sql);
		}
	}
}
